#!/usr/bin/python
# -*- coding: UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
Interaction between file and mysql data

Authors: xiaojianghui
Date:    10/22/2020 11:01 AM
"""

import xlrd
import os
import shutil


def crawl_urls(file_name):
    """
        Read excel table content and CVE score
    :param file_name: excel file
    :return urls: list
    """
    urls = []
    try:
        data = xlrd.open_workbook("./newexcels/" + file_name)
        # Find the vulnerability report information table by name
        table_one = data.sheet_by_name("Sheet1")
        # Get the maximum number of rows of all data
        row_number = table_one.nrows
        for i in range(1, row_number):
            url = "https://nvd.nist.gov/vuln/detail/" + str(table_one.cell(i, 2).value)
            urls.append(url)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    return urls


def crawl_cve_num(file_name):
    """
        CVE number
    :param file_name:excel file
    :return cve_num:list
    """
    cve_num = []
    try:
        data = xlrd.open_workbook("./newexcels/" + file_name)
        # Find the vulnerability report information table by name
        table_one = data.sheet_by_name("Sheet1")
        # Get the maximum number of rows of all data
        row_number = table_one.nrows
        for i in range(1, row_number):
            cve_num_one = str(table_one.cell(i, 2).value)
            cve_num.append(cve_num_one)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    return cve_num


def crawl_cve_version(file_name):
    """
        CVE version
    :param file_name:excel file
    :return cve_version:list
    """
    cve_version = []
    try:
        data = xlrd.open_workbook("./newexcels/" + file_name)
        table_one = data.sheet_by_name("Sheet1")
        row_number = table_one.nrows
        for i in range(1, row_number):
            cve_version_one = table_one.cell(i, 1).value
            cve_version.append(cve_version_one)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    return cve_version


def crawl_packname(file_name):
    """
        Component
    :param file_name:excel file
    :return pack_name:string
    """
    pack_name = []
    try:
        data = xlrd.open_workbook("./newexcels/" + file_name)
        table_one = data.sheet_by_name("Sheet1")
        row_number = table_one.nrows
        for i in range(1, row_number):
            pack_name_one = table_one.cell(i, 0).value
            pack_name.append(pack_name_one)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    return pack_name


def move_file(file_name):
    """
        excel After the analysis is complete, call the function to move to the oldexcels folder for backup
    :param file_name:excel file
    """
    try:
        f_src = os.path.join("./newexcels/" + file_name)
        if not os.path.isdir("./oldexcels/"):
            os.mkdir("./oldexcels/")
        f_dst = os.path.join("./oldexcels/" + file_name)
        shutil.move(f_src, f_dst)
    except SystemExit as e:
        print("move error", e)
